01. Introduction

Lesson Overview Heading

Introduction

ND004 C01 L03 01 Lesson Overview

Takeaways + A Note on ORMS

SQLAlchemy

  • SQLAlchemy is the most popular open-source library for working with relational databases from Python.
  • It is one type of ORM library, AKA an Object-Relational Mapping library, which provides an interface for using object oriented programming to interact with a database.

Other ORM libraries that exist across other languages include popular choices like javascript libraries Sequelize and Bookshelf.js for NodeJS applications, the ruby library ActiveRecord , which is used inside Ruby on Rails , and CakePHP for applications written on PHP, amongst many other such ORMs.

Note on ORMs: are they a "best practice"?

Using an ORM to interact with your database is simply one of many valid approaches for how you can add additional layers of abstraction to your web application to interact with a database more easily. There are other kinds of query builder libraries you can use that are between talking to a database directly (with a database driver library like pyscopg2), and using an ORM. An ORM is considered to be the highest level of abstraction you can add to a web application for database management. Query Builder libraries are somewhere in the middle. There are many mixed opinions about whether ORMs are a best practice approach in all cases, such as this opinion on "Why you should avoid ORMs" .

Thankfully, SQLAlchemy happens to offer multiple levels of abstraction you can prefer, between the database driver and the ORM, so you can customize the development of your web application to your own liking. The many granular levels of abstraction that SQLAlchemy offers, which we will touch upon in the next lesson, is one of the reasons that has led to its widespread popularity and diverse ways of using the library and approaching your database programming.

SQLAlchemy:

  • Features function-based query construction : allows SQL clauses to be built via Python functions and expressions.

  • Avoid writing raw SQL . It generates SQL and Python code for you to access tables, which leads to less database-related overhead in terms of the volume of code you need to write overall to interact with your models.

  • Moreover, you can avoid sending SQL to the database on every call . The SQLAlchemy ORM library features automatic caching , caching collections and references between objects once initially loaded.

What is SQLAlchemy?

SOLUTION: A Python library for working with relational databases

What does ORM stand for?

SOLUTION: Object Relational Mapping

What does an ORM do?

SOLUTION: Maps tables and columns to class objects and attributes.

An ORM allows you to…

SOLUTION: Interact with a database using an object-oriented language like Python, rather than writing raw SQL.

Suppose you have the following code for querying a database: 

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  description VARCHAR NOT NULL
);

Would this query work with any database management system you're working with?

SOLUTION: No—this code will only work in some systems

Select all of the reasons to use SQLALchemy over writing raw SQL:

SOLUTION:
  • Write less bug-prone code
  • Work entirely in Python
  • Be able to switch database systems easily without rewriting code

Next Concept